package org.xqh.test.yzs.shimao.hongxu;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import org.xqh.utils.DateUtil;
import org.xqh.utils.excel.ExcelReader;
import org.xqh.utils.file.ReadTxtFileUtils;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @ClassName GenerateSpaceSql
 * @Description TODO
 * @Author xuqianghui
 * @Date 2020/9/9 10:24
 * @Version 1.0
 */
public class GenerateSpaceSql {

    private static AtomicInteger idx = new AtomicInteger(1200000);//ID从120万开始
    private static AtomicInteger idx2 = new AtomicInteger(1201500);//缺失数据 id

    public static final String WORK_PATH = "E:\\document\\yzs\\program\\shimao-iot\\space-server\\虹旭小区\\";
    public static SpaceTreeDto parseInsertSql() {
        StringBuffer sb = new StringBuffer();
        List<String> list = ReadTxtFileUtils.readTxt(new File(WORK_PATH+"虹旭小区-new.json"));
//        File out = new File("C:\\Users\\YZS\\Desktop\\out.sql");
        list.stream().filter(s-> StringUtils.isNotBlank(s)).forEach(s-> {
            sb.append(s.trim());
        });
        Long rootId = 2000109L;
        Long pid = 2000246L;
        JSONObject json = JSON.parseObject(sb.toString());
        List<String> sqlList = Lists.newArrayList();
        SpaceTreeDto spaceTree = JSON.parseObject(json.getString("data"), SpaceTreeDto.class);
        spaceTree.setId(pid);
        spaceTree.setRootId(rootId);
        spaceTree.setPid(rootId);
        spaceTree.setSpaceTreeInfo(Lists.newArrayList(pid));
        sqlList.add(getInsertSql(spaceTree));//第一条数据 不加
        recursionPutSpaceInfo(spaceTree, spaceTree.getSpaceEntityList(), sqlList);
//        ReadTxtFileUtils.writeToTxt(sqlList, WORK_PATH+"虹旭小区-new2.sql");
//        ReadTxtFileUtils.writeToTxt(Arrays.asList(printJson.toJSONString()), "C:\\Users\\YZS\\Desktop\\虹旭小区-new.sql");
        return spaceTree;
    }

    public static void main(String[] args) {
        List<XQModel> list = parseExcel();
        SpaceTreeDto spaceTree = parseInsertSql();
        List<XQModel> err = Lists.newArrayList();
        list.stream().forEach(s-> {
            Optional<SpaceTreeDto> ld = spaceTree.getSpaceEntityList().stream().filter(l-> l.getSpaceName().equals(s.getName())).findAny();
            if(ld.isPresent()){
                ld.get().setOrderShow(s.getSort());
                //遍历下级
                s.getItems().stream().forEach(sc-> {
                    Optional<SpaceTreeDto> lc = ld.get().getSpaceEntityList().stream().filter(c-> c.getSpaceName().equals(sc.getName())).findAny();
                    if(lc.isPresent()){
                        lc.get().setOrderShow(sc.getSort());
                        //遍历下级
                        sc.getItems().stream().forEach(sl-> {
                            Optional<SpaceTreeDto> ll = lc.get().getSpaceEntityList().stream().filter(c-> c.getSpaceName().equals(sl.getName())).findAny();
                            if(ll.isPresent()){
                                ll.get().setOrderShow(sl.getSort());
                            }else {
                                err.add(sl);
                                SpaceTreeDto errData = JSON.parseObject(JSON.toJSONString(lc.get().getSpaceEntityList().get(0)), SpaceTreeDto.class);
                                errData.setId(Long.valueOf(idx2.getAndIncrement()));
                                errData.setSpaceName(sl.getName());
                                List<Long> spaceTreeInfo = Lists.newArrayList(lc.get().getSpaceEntityList().get(0).getSpaceTreeInfo());
                                spaceTreeInfo.remove(spaceTreeInfo.size() - 1);
                                spaceTreeInfo.add(errData.getId());
                                errData.setSpaceTreeInfo(spaceTreeInfo);
                                lc.get().getSpaceEntityList().add(errData);
                                System.out.println(getInsertSql(errData));
                            }
                        });
                    }else {
                        err.add(sc);
                    }
                });
            }else {
                err.add(s);
            }
        });
        System.out.println(JSON.toJSONString(err));
        JSONObject printJson = new JSONObject();
        printJson.put("retcode", 0);
        printJson.put("msg", "success");
        printJson.put("data", spaceTree);
        ReadTxtFileUtils.writeToTxt(Arrays.asList(JSON.toJSONString(printJson)), WORK_PATH+"虹旭小区-new2.json");
    }

    public static String formatStr(String str){
        if(str.endsWith(".0")){
            str = str.substring(0, str.length() - 2);
        }
        return str;
    }

    public static List<XQModel> parseExcel(){
        File file = new File("E:\\document\\yzs\\program\\shimao-iot\\space-server\\虹旭小区\\虹旭小区项目楼栋门牌号0728_最终版.xlsx");
        XQModel model1 = parseOneSheet("虹旭小区", file, 0, 1);
        XQModel model2 = parseOneSheet("虹旭二小区", file, 1, 2);
        XQModel model3 = parseOneSheet("中星雅苑", file, 2, 3);
        XQModel model4 = parseOneSheet("虹警新苑", file, 3, 4);
        return Arrays.asList(model1, model2, model3, model4);
    }

    public static XQModel parseOneSheet(String sheetName, File file, int sheetIndex, int sort){
        List<XQModel> items = Lists.newArrayList();
        XQModel model = XQModel.builder()
                .name(sheetName)
                .items(items)
                .sort(sort)
                .build();
        List<String[]> list = ExcelReader.getExcelData(file, 1, sheetIndex);
        XQModel tmp = null;
        for(String[] arr:list){
            if(Objects.nonNull(arr[0])){
                String name = formatStr(arr[0]);
                tmp = XQModel.builder()
                        .parent(model.getName())
                        .name(name)
                        .build();
                List<XQModel> clist = Lists.newArrayList();
                tmp.setItems(clist);
                items.add(tmp);
            }
            addChild(arr[1], tmp);
            addChild(arr[2], tmp);
            addChild(arr[3], tmp);
            addChild(arr[4], tmp);
        }
        for(int i=0;i<model.getItems().size();i++){
            XQModel item = model.getItems().get(i);
            item.setSort(i+1);
            for(int j = 0; j<item.getItems().size();j++){
                item.getItems().get(j).setSort(j+1);
            }
        }
        return model;
    }

    public static void addChild(String str, XQModel tmp){
        if(Objects.nonNull(str)){
            String name = formatStr(str);
            tmp.getItems().add(XQModel.builder()
                    .name(name)
                    .parent(tmp.getParent()+ "-" + tmp.getName())
                    .build());
        }
    }

    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    public static class XQModel{

        private String name;

        private int sort;//排序

        private String parent;

        private List<XQModel> items;
    }

    public static void recursionPutSpaceInfo(SpaceTreeDto parent, List<SpaceTreeDto> childList, List<String> sqlList){
        if(!CollectionUtils.isEmpty(childList)){
            childList.stream().forEach(c-> {
                c.setId(Long.valueOf(idx.getAndIncrement()));
                c.setPid(parent.getId());
                c.setRootId(parent.getRootId());
                List<Long> spaceTreeInfo = Lists.newArrayList();
                spaceTreeInfo.addAll(parent.getSpaceTreeInfo());
                spaceTreeInfo.add(c.getId());
                c.setSpaceTreeInfo(spaceTreeInfo);
                sqlList.add(getInsertSql(c));
                if(!CollectionUtils.isEmpty(c.getSpaceEntityList())){
                    recursionPutSpaceInfo(c, c.getSpaceEntityList(), sqlList);
                }
            });
        }
    }

    public static String sql = "INSERT INTO shimao_space (id, space_name, measure, space_type, remarks, pid, root_id,order_show, create_time, update_time, create_name, is_suite, is_special_mode, status, is_project, province_code, city_code, icon) VALUES (";

    public static String getInsertSql(SpaceTreeDto space){
        StringBuffer sb = new StringBuffer(sql);
        sb.append(space.getId()).append(",");
        sb.append(getStr(space.getSpaceName())).append(",");
        sb.append("null").append(",");
        sb.append(space.getSpaceType()).append(",");
        sb.append(getStr(space.getRemarks())).append(",");
        sb.append(space.getPid()).append(",");
        sb.append(space.getRootId()).append(",");
        sb.append(Objects.isNull(space.getOrderShow()) ? 99 : space.getOrderShow()).append(",");
        sb.append(getDateStr(space.getCreateTime())).append(",");
        sb.append(getDateStr(space.getUpdateTime())).append(",");
        sb.append(getStr(space.getCreateName())).append(",");
        sb.append(getBoolean(space.getIsSuite())).append(",");
        sb.append(getBoolean(space.getIsSpecialMode())).append(",");
        sb.append(space.getStatus()).append(",");
        sb.append(getBoolean(space.getIsProject())).append(",");
        sb.append(space.getProvinceCode()).append(",");
        sb.append(space.getCityCode()).append(",");
        sb.append(getStr(space.getIcon()));
        sb.append(");");
        return sb.toString();
    }

    public static int getBoolean(Boolean val){
        return Objects.nonNull(val) && val ? 1 : 0;
    }

    public static String getStr(String str){
        return Objects.isNull(str) ? null : "'"+str+"'";
    }

    public static String getDateStr(Date date){
        return Objects.nonNull(date) ? ("'"+ DateUtil.formatDate(date, "yyyy-MM-dd HH:mm:ss") +"'") : null;
    }
}
